package entity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Set;
import javax.ejb.CreateException;
import javax.ejb.EJBException;
import javax.ejb.EntityBean;
import javax.ejb.EntityContext;
import javax.ejb.FinderException;
import javax.ejb.NoSuchEntityException;
import javax.ejb.ObjectNotFoundException;
import javax.ejb.RemoveException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import report.filter.ReportFilter;
import transfer.Address;
import transfer.Commodity;
import transfer.Order;
import transfer.OrderReport;
import transfer.OrderState;
import transfer.User;

/**
 *
 * @author Oleh
 */
public class Orders implements EntityBean {

    private EntityContext entityContext;
    private DataSource dataSource;
    private Order order;
    public static final int roundPrecision = 2;

    public void setEntityContext(EntityContext entityContext) throws EJBException {
        try {
            this.entityContext = entityContext;
            Context context = new InitialContext();
            dataSource = (DataSource) context.lookup("jdbc/ossystem");
        } catch (NamingException ex) {
            ex.printStackTrace();
        }
    }

    public void unsetEntityContext() throws EJBException {
        entityContext = null;
        dataSource = null;
        order = null;
    }

    public void ejbRemove() throws RemoveException, EJBException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "DELETE FROM Orders WHERE id_order = ?");
            statement.setInt(1, order.getIdOrder().intValue());
            if (statement.executeUpdate() < 1) {
                throw new RemoveException("Error DELETE order");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection, statement);
        }
    }

    public void ejbActivate() throws EJBException {
    }

    public void ejbPassivate() throws EJBException {
    }

    public void ejbLoad() throws EJBException {
        Integer primaryKey = (Integer) entityContext.getPrimaryKey();
        Connection connection = null;
        PreparedStatement statement = null;
        order = new Order();
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_warehouse, id_order_state, id_customer, "
                    + "creation_date, delivered_date, id_address, total_cost, "
                    + "unregistered_e_mail, is_subscribed, id_warehouse_employee, "
                    + "id_courier, cost_commodities, cost_delivery, distance "
                    + "FROM Orders WHERE id_order = ?");
            statement.setInt(1, primaryKey.intValue());
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new NoSuchEntityException("No such entity Orders");
            }
            order.setIdOrder(primaryKey);

            Integer idWarehouse = new Integer(resultSet.getInt(1));
            if (!idWarehouse.equals(new Integer(0))) {
                Context c = new InitialContext();
                WarehouseLocalHome warehouseHome = (WarehouseLocalHome) c.lookup("java:comp/env/Warehouse");
                try {
                    WarehouseLocal warehouseLocal = (WarehouseLocal) warehouseHome.findByPrimaryKey(idWarehouse);
                    order.setWarehouse(warehouseLocal.getWarehouse());
                } catch (FinderException e) {
                    order.setWarehouse(null);
                }
            } else {
                order.setWarehouse(null);
            }
            Integer idOrderState = new Integer(resultSet.getInt(2));
            order.setOrderState(OrderState.instance(idOrderState));

            Integer idUser = new Integer(resultSet.getInt(3));
            if (!idUser.equals(new Integer(0))) {
                Context c = new InitialContext();
                UsersLocalHome userHome = (UsersLocalHome) c.lookup("java:comp/env/Users");
                try {
                    UsersLocal userLocal = userHome.findByPrimaryKey(idUser);
                    order.setCustomer(userLocal.getUser());
                } catch (FinderException e) {
                    order.setCustomer(null);
                }
            } else {
                order.setCustomer(null);
            }
            order.setCreationDate( resultSet.getDate(4));
            order.setDeliveredDate(resultSet.getDate(5));

            Integer idAddress = new Integer(resultSet.getInt(6));
            if (!idAddress.equals(new Integer(0))) {
                Context c = new InitialContext();
                AddressLocalHome addressHome = (AddressLocalHome) c.lookup("java:comp/env/Address");
                try {
                    AddressLocal addressLocal = addressHome.findByPrimaryKey(idAddress);
                    order.setAddress(addressLocal.getAddress());
                } catch (FinderException e) {
                    order.setAddress(null);
                }
            } else {
                order.setAddress(null);
            }

            order.setTotalCost(new Double(resultSet.getDouble(7)));
            order.setEmail((String) resultSet.getObject(8));
            order.setIsSubscribed(new Boolean(resultSet.getBoolean(9)));
            
            Integer idWarehouseEmployee = new Integer(resultSet.getInt(10));
            if (!idWarehouseEmployee.equals(new Integer(0))) {
                Context c = new InitialContext();
                UsersLocalHome userHome = (UsersLocalHome) c.lookup("java:comp/env/Users");
                try {
                    UsersLocal userLocal = userHome.findByPrimaryKey(idWarehouseEmployee);
                    order.setWarehouseEmployee(userLocal.getUser());
                } catch (FinderException e) {
                    order.setWarehouseEmployee(null);
                }
            } else {
                order.setWarehouseEmployee(null);
            }
            
            Integer idCourier = new Integer(resultSet.getInt(11));
            if (!idCourier.equals(new Integer(0))) {
                Context c = new InitialContext();
                UsersLocalHome userHome = (UsersLocalHome) c.lookup("java:comp/env/Users");
                try {
                    UsersLocal userLocal = userHome.findByPrimaryKey(idCourier);
                    order.setCourier(userLocal.getUser());
                } catch (FinderException e) {
                    order.setCourier(null);
                }
            } else {
                order.setCourier(null);
            }
            
            order.setCostCommodities(new Double(resultSet.getDouble(12)));
            order.setCostDelivery(new Double(resultSet.getDouble(13)));
            order.setDistance(new Double(resultSet.getDouble(14)));

            statement = connection.prepareStatement(
                    "SELECT id_commodity, quantity "
                    + "FROM Order_link_Commodity WHERE id_order = ?");
            statement.setInt(1, primaryKey.intValue());
            HashMap map = new HashMap();
            Context c = new InitialContext();
            CommodityLocalHome commodityHome = (CommodityLocalHome) c.lookup("java:comp/env/Commodity");
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Integer idCommodity = new Integer(resultSet.getInt(1));
                CommodityLocal commodityLocal = commodityHome.findByPrimaryKey(idCommodity);
                Integer quantity = new Integer(resultSet.getInt(2));
                map.put(commodityLocal.getCommodity(), quantity);
            }
            order.setCommodities(map);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (FinderException ex) {
            ex.printStackTrace();
        } catch (NamingException ex) {
            ex.printStackTrace();
        } finally {
            closeConnection(connection, statement);
        }
    }

    public void ejbStore() throws EJBException {
        Connection connection = null;
        PreparedStatement statement = null;
        
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "UPDATE Orders SET id_warehouse = ?, id_order_state = ?, "
                    + "id_customer = ?, creation_date = ?, delivered_date = ?, "
                    + "id_address = ?, total_cost = ROUND(?, " + roundPrecision + "), "
                    + "unregistered_e_mail = ?, is_subscribed  = ?, "
                    + "id_warehouse_employee = ?, id_courier = ?, "
                    + "cost_commodities = ROUND(?, " + roundPrecision + "), "
                    + "cost_delivery = ROUND(?, " + roundPrecision + "), distance = ? "
                    + "WHERE id_order = ?");
            if (order.getWarehouse() != null) {
                statement.setInt(1, order.getWarehouse().getIdWarehouse().intValue());
            } else {
                statement.setNull(1, Types.INTEGER);
            }

            statement.setObject(2, order.getOrderState().getIdOrderState(), Types.INTEGER);
            if (order.getCustomer() != null) {
                statement.setInt(3, order.getCustomer().getIdUser().intValue());
            } else {
                statement.setNull(3, Types.INTEGER);
            }
            if (order.getCreationDate() != null) {
                statement.setDate(4, new java.sql.Date(order.getCreationDate().getTime()));
            } else {
                statement.setNull(4, Types.DATE);
            }
            if (order.getDeliveredDate() != null) {
                statement.setDate(5, new java.sql.Date(order.getDeliveredDate().getTime()));
            } else {
                statement.setNull(5, Types.DATE);
            }
            if (order.getAddress() != null) {
                statement.setInt(6, order.getAddress().getIdAddress().intValue());
            } else {
                statement.setNull(6, Types.INTEGER);
            }
            
            if (order.getTotalCost() != null) {
                statement.setDouble(7, order.getTotalCost().doubleValue());
            } else {
                statement.setDouble(7, 0);
            }
             
            statement.setObject(8, order.getEmail(), Types.VARCHAR);
            
            if (order.getIsSubscribed() != null) {
                statement.setBoolean(9, order.getIsSubscribed().booleanValue());
            } else {
                statement.setNull(9, Types.INTEGER);
            }
            
            if (order.getWarehouseEmployee() != null) {
                statement.setInt(10, order.getWarehouseEmployee().getIdUser().intValue());
            } else {
                statement.setNull(10, Types.INTEGER);
            }
            
            if (order.getCourier() != null) {
                statement.setInt(11, order.getCourier().getIdUser().intValue());
            } else {
                statement.setNull(11, Types.INTEGER);
            }
            
            if (order.getCostCommodities() != null) {
                statement.setDouble(12, order.getCostCommodities().doubleValue());
            } else {
                statement.setDouble(12, 0);
            }
            
            if (order.getCostDelivery() != null) {
                statement.setDouble(13, order.getCostDelivery().doubleValue());
            } else {
                statement.setDouble(13, 0);
            }
            
            if (order.getDistance()!= null) {
                statement.setDouble(14, order.getDistance().doubleValue());
            } else {
                statement.setDouble(14, 0);
            }
            
            statement.setInt(15, ((Integer) entityContext.getPrimaryKey()).intValue());
            statement.executeUpdate();

            statement = connection.prepareStatement(
                    "DELETE FROM Order_link_Commodity WHERE id_order = ?");
            statement.setInt(1, ((Integer) entityContext.getPrimaryKey()).intValue());
            statement.executeUpdate();

            HashMap map = order.getCommodities();
            if (map != null) {
                Set keys = map.keySet();
                Iterator iterator = keys.iterator();
                while (iterator.hasNext()) {
                    statement = connection.prepareStatement(
                            "INSERT INTO Order_link_Commodity (id_order, id_commodity, quantity) VALUES (?, ?, ?)");
                    statement.setInt(1, order.getIdOrder().intValue());
                    Commodity key = (Commodity) iterator.next();
                    Integer quantity = (Integer) map.get(key);
                    statement.setInt(2, key.getIdCommodity().intValue());
                    statement.setInt(3, quantity.intValue());
                    statement.executeUpdate();
                }
            }
        } catch (SQLException e) {
            throw new EJBException(e);
        } finally {
            closeConnection(connection, statement);;
        }
    }

    public java.lang.Integer ejbFindByPrimaryKey(Integer idOrder) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_order "
                    + "FROM Orders WHERE id_order = ?");
            statement.setInt(1, idOrder.intValue());
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new ObjectNotFoundException("Order with primary key " + idOrder + " not exists");
            }
            return idOrder;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public Collection ejbFindAllUndelivered() throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            ArrayList orders = new ArrayList();
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_order "
                    + "FROM Orders WHERE delivered_date IS NULL");
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Integer idOrder = new Integer(resultSet.getInt(1));
                orders.add(idOrder);
            }
            return orders;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public Collection ejbFindAllShipping(User courier) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            ArrayList orders = new ArrayList();
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_order "
                    + "FROM Orders WHERE id_order_state = ? AND id_courier = ?");
            statement.setInt(1, OrderState.ID_SHIPPING.intValue());
            statement.setInt(2, courier.getIdUser().intValue());
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Integer idOrder = new Integer(resultSet.getInt(1));
                orders.add(idOrder);
            }
            return orders;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public Collection ejbFindAllQueued(User warehouse_employee) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            ArrayList orders = new ArrayList();
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_order "
                    + "FROM Orders WHERE id_order_state = ? AND id_warehouse_employee = ?");
            statement.setInt(1, OrderState.ID_QUEUED.intValue());
            statement.setInt(2, warehouse_employee.getIdUser().intValue());
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Integer idOrder = new Integer(resultSet.getInt(1));
                orders.add(idOrder);
            }
            return orders;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public void ejbPostCreate(Order order) throws CreateException {
    }

    public java.lang.Integer ejbCreate(Order order) throws CreateException {
        Connection connection = null;
        PreparedStatement statement = null;
        Integer idOrder = null;
        try {
            connection = dataSource.getConnection();
            Statement stat = connection.createStatement();
            ResultSet resultSet = stat.executeQuery("select seq.nextval from dual");

            if (resultSet.next()) {
                idOrder = new Integer(resultSet.getInt(1));
            }
            stat.close();

            this.order = order;
            this.order.setIdOrder(idOrder);

            statement = connection.prepareStatement("INSERT INTO Orders "
                    + "(id_order, id_warehouse, id_order_state, id_customer, "
                    + "creation_date, delivered_date, id_address, total_cost, "
                    + "unregistered_e_mail, is_subscribed, "
                    + "id_warehouse_employee, id_courier, "
                    + "cost_commodities, cost_delivery, distance) "
                    + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            statement.setInt(1, idOrder.intValue());

            if (order.getWarehouse() != null) {
                statement.setInt(2, order.getWarehouse().getIdWarehouse().intValue());
            } else {
                statement.setNull(2, Types.INTEGER);
            }
            statement.setInt(3, order.getOrderState().getIdOrderState().intValue());
            if (order.getCustomer() != null) {
                statement.setInt(4, order.getCustomer().getIdUser().intValue());
            } else {
                statement.setNull(4, Types.INTEGER);
            }
            if (order.getCreationDate() != null) {
                statement.setDate(5, new java.sql.Date(order.getCreationDate().getTime()));
            } else {
                statement.setNull(5, Types.DATE);
            }
            if (order.getDeliveredDate() != null) {
                statement.setDate(6, new java.sql.Date(order.getDeliveredDate().getTime()));
            } else {
                statement.setNull(6, Types.DATE);
            }
            if (order.getAddress() != null) {
                statement.setInt(7, order.getAddress().getIdAddress().intValue());
            } else {
                statement.setNull(7, Types.INTEGER);
            }
            statement.setObject(8, order.getTotalCost(), Types.DOUBLE);
            statement.setObject(9, order.getEmail(), Types.VARCHAR);
            if (order.getIsSubscribed() != null) {
                statement.setBoolean(10, order.getIsSubscribed().booleanValue());
            } else {
                statement.setNull(10, Types.INTEGER);
            }
            
            if (order.getWarehouseEmployee() != null) {
                statement.setInt(11, order.getWarehouseEmployee().getIdUser().intValue());
            } else {
                statement.setNull(11, Types.INTEGER);
            }
            
            if (order.getCourier() != null) {
                statement.setInt(12, order.getCourier().getIdUser().intValue());
            } else {
                statement.setNull(12, Types.INTEGER);
            }
            
            statement.setObject(13, order.getCostCommodities(), Types.DOUBLE);
            statement.setObject(14, order.getCostDelivery(), Types.DOUBLE);
            statement.setObject(15, order.getDistance(), Types.DOUBLE);
            
            if (statement.executeUpdate() != 1) {
                throw new CreateException("Inserted error");
            }

            HashMap map = order.getCommodities();
            if (map != null) {
                Set keys = map.keySet();
                Iterator iterator = keys.iterator();
                while (iterator.hasNext()) {
                    statement = connection.prepareStatement(
                            "INSERT INTO Order_link_Commodity (id_order, id_commodity, quantity) VALUES (?, ?, ?)");
                    statement.setInt(1, order.getIdOrder().intValue());
                    Commodity key = (Commodity) iterator.next();
                    Integer quantity = (Integer) map.get(key);
                    statement.setInt(2, key.getIdCommodity().intValue());
                    statement.setInt(3, quantity.intValue());
                    statement.executeUpdate();
                }
            }
            return idOrder;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    private void closeConnection(Connection connection, Statement statement) {
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public Order getOrder() {
        return order;
    }

    public void setOrder(Order order) {
        this.order = order;
    }

    public boolean ejbHomeIsCartExist(User user) {
        try {
            if (ejbFindCart(user) != null) {
                return true;
            }
            return false;
        } catch (FinderException e) {
            e.printStackTrace();
            return false;
        }
    }

    public Integer ejbFindCart(User user) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_order "
                    + "FROM Orders WHERE id_customer = ? AND id_order_state = ?");
            statement.setInt(1, user.getIdUser().intValue());
            statement.setInt(2, OrderState.ENTERING.getIdOrderState().intValue());
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new ObjectNotFoundException("Cart for user not exists");
            }
            return new Integer(resultSet.getInt(1));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public Collection ejbFindAllOrdersForUser(User user) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            ArrayList orders = new ArrayList();
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_order "
                    + "FROM Orders WHERE id_customer = ? ORDER BY creation_date DESC, id_order DESC");
            statement.setInt(1, user.getIdUser().intValue());
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Integer idOrder = new Integer(resultSet.getInt(1));
                orders.add(idOrder);
            }
            return orders;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public Collection ejbFindOrdersReport(ArrayList filters, String orderBy, Integer startRow, Integer quantity) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            ArrayList orders = new ArrayList();
            String query = "SELECT id_order, rownum rn FROM Orders WHERE id_order_state <> 1 ";
            if (filters != null && filters.size() > 0) {
                query += " AND \n";
                int size = filters.size();
                for (int i = 0; i < size; i++) {
                    ReportFilter filter = (ReportFilter) filters.get(i);
                    query += filter.getPartSqlStatement();
                    if (i != size - 1) query += " AND \n";
                }
            }
            if (orderBy != null && !orderBy.equals("")) {
                query += " ORDER BY " + orderBy;
            }
            if (startRow == null) {
                startRow = new Integer(1);
            }
            query = "SELECT id_order FROM ( " + query + " ) WHERE rn >= " + startRow;
            if (quantity != null) {
                query += " AND rn < " + (startRow.intValue() + quantity.intValue());
            }
            System.out.println("query = " + query);
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(query);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Integer idOrder = new Integer(resultSet.getInt(1));
                orders.add(idOrder);
            }
            return orders;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public OrderReport getOrderReport() {
        OrderReport report = new OrderReport();
        report.idOrder = order.getIdOrder();
        report.orderState = order.getOrderState().getNameOrderState();
        report.nameWarehouse = order.getWarehouse().getNameWarehouse();
        report.creationDate = order.getCreationDate();
        report.deliveredDate = order.getDeliveredDate();
        Address address = order.getAddress();
        if (address == null) {
            address = order.getCustomer().getAddress();
        }
        report.longitude = address.getLongitude();
        report.latitude = address.getLatitude();
        report.distance = order.getDistance();
        report.costCommodities = order.getCostCommodities();
        report.costDelivery = order.getCostDelivery();
        report.totalCost = order.getTotalCost();
        User customer = order.getCustomer();
        if (customer != null) {
            report.login = customer.getLogin();
        } else {
            report.login = "anonymous customer";
        }
        return report;
    }
    
}
